﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using org.in2bits.MyXls;
using System.Diagnostics;

namespace Relocation.Classes
{
    /// <summary>
    /// 从DataGridView导出数据到Excel
    /// </summary>
    class GridToExcel
    {
        /// <summary>
        /// 执行打印
        /// </summary>
        /// <param name="dataGridView"></param>
        /// <param name="filePath">文件路径</param>
        static public void print(DataGridView dataGridView)
        {
            String fileFullName = GetFileName();
            if (String.IsNullOrEmpty(fileFullName))
                return;
            String filePath = Path.GetDirectoryName(fileFullName);
            String fileName = Path.GetFileName(fileFullName);
            DataGridViewColumnCollection columns = dataGridView.Columns;
            StringBuilder sb = new StringBuilder();
            List<String> columnNames = new List<string>();

            try
            {
                foreach (DataGridViewColumn column in columns)
                {
                    if (!column.Displayed)
                        continue;
                    String headerText = column.HeaderText;
                    if (!headerText.StartsWith(","))
                        headerText = "&" + headerText;
                    sb.Append(headerText);
                    columnNames.Add(column.Name);
                }
                if (sb.Length > 0)
                {
                    sb.Remove(0, 1);
                }
            }
            catch (Exception)
            {
            }

            String names = sb.ToString();
            String[] columnTexts = names.Split('&');
            //是否又多行表头
            bool isMulitLine = names.Contains(',');
            int rowIndex = 1;
            XlsDocument xls = new XlsDocument();
            XF xfTitle = xls.NewXF();
            xfTitle.HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
            xfTitle.VerticalAlignment = VerticalAlignments.Centered;
            xfTitle.Font.Bold = true;

            Worksheet sheet = xls.Workbook.Worksheets.Add("sheet");
            Cells cells = sheet.Cells;

            int leave = 0;
            TreeView treeView = GetTreeView(dataGridView, ref leave);
            //绘制表头
            makeHeads(treeView, cells, xfTitle, leave);
            rowIndex += leave - 1;

            //数据            
            foreach (DataGridViewRow row in dataGridView.Rows)
            {
                rowIndex++;
                int cellIndex = 1;  //起点为1
                foreach (String colName in columnNames)
                {
                    XF xfValue = xls.NewXF();
                    DataGridViewCell g_cell = row.Cells[colName];
                    if (g_cell == null)
                        continue;

                    Object value = g_cell.FormattedValue;
                    Type type = g_cell.FormattedValueType;

                    String valueString = "";


                    if (type.Equals(typeof(decimal)) || type.Equals(typeof(System.Nullable<decimal>)) || g_cell.Style.Format.ToLower().Equals("n2"))
                    {
                        if (value != null)
                        {
                            valueString = ((decimal)value).ToString("G0");
                        }
                        else
                        {
                            valueString = "0";
                        }
                        xfValue.UseNumber = true;
                        xfValue.HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Right;
                    }
                    else if (type.Equals(typeof(DateTime)) || type.Equals(typeof(System.Nullable<DateTime>)))
                    {
                        if (value != null)
                        {
                            valueString = ((DateTime)value).ToString("yyyy年MM月dd日");
                        }
                    }
                    else
                    {
                        if (value != null)
                        {
                            valueString = value.ToString();
                        }
                    }
                    cells.Add(rowIndex, cellIndex++, valueString, xfValue);
                }

            }
            xls.FileName = fileName;
            xls.Save(filePath, true);

            if (File.Exists(fileFullName))
            {
                Process.Start(fileFullName);
            }

        }

        /// <summary>
        /// 绘制表头
        /// </summary>
        /// <param name="treeView">多行表头对象</param>
        /// <param name="cells">sheet.Cells</param>
        /// <param name="xf">表头的单元格样式</param>
        /// <param name="leave">表头最大深度（多行表头）</param>
        static private void makeHeads(TreeView treeView, Cells cells, XF xf, int leave)
        {
            int colIndex = 1;
            foreach (TreeNode node in treeView.Nodes)
            {
                makeHeads(node, cells, ref colIndex, leave, xf);
            }
        }

        static private void makeHeads(TreeNode treeNode, Cells cells, ref int colIndex, int leave, XF xf)
        {
            int colCount = getNodeColCount(treeNode);   //此节点占用的列数
            int rowCount = getChildsRowCount(treeNode, leave);  //此节点占用的行数
            int rowIndex = treeNode.Level + 1;
            cells.Add(rowIndex, colIndex, treeNode.Text, xf);

            cells.Merge(rowIndex, rowIndex + rowCount - 1, colIndex, colIndex + colCount - 1);
            if (treeNode.Nodes.Count > 0)
            {
                foreach (TreeNode node in treeNode.Nodes)
                {
                    makeHeads(node, cells, ref colIndex, leave, xf);
                }
            }
            else
            {
                colIndex += colCount;

            }

        }

        static private TreeView GetTreeView(DataGridView dataGridView, ref int maxLevel)
        {
            TreeView tnc = new TreeView();
            foreach (DataGridViewColumn column in dataGridView.Columns)
            {
                if (!column.Visible)
                    continue;
                string[] headTexts = column.HeaderText.Split(',');
                if (maxLevel < headTexts.Length) maxLevel = headTexts.Length;
                TreeNode pnode = null;
                for (int i = 0; i < headTexts.Length; i++)
                {
                    if (headTexts[i] == "")
                    {
                        if (i == 0) pnode = (tnc.Nodes.Count > 0) ? tnc.Nodes[tnc.Nodes.Count - 1] : null;
                        else pnode = pnode.Nodes[pnode.Nodes.Count - 1];
                    }
                    else
                    {
                        TreeNode node = new TreeNode(headTexts[i]);
                        if (i == 0) tnc.Nodes.Add(node);
                        else pnode.Nodes.Add(node);
                        pnode = node;
                    }
                }

            }

            return tnc;
        }


        /// <summary>
        /// 根据DataGridView生成多行表头的TreeVIew
        /// </summary>
        /// <param name="dataGridView">dataGridView</param>
        /// <returns>TreeView</returns>
        static private TreeView GetTreeView(DataGridView dataGridView)
        {
            TreeView tnc = new TreeView();
            foreach (DataGridViewColumn column in dataGridView.Columns)
            {
                string[] headTexts = column.HeaderText.Split(',');

                TreeNode pnode = null;
                for (int i = 0; i < headTexts.Length; i++)
                {

                    if (headTexts[i] == "")
                    {
                        if (i == 0) pnode = (tnc.Nodes.Count > 0) ? tnc.Nodes[tnc.Nodes.Count - 1] : null;
                        else pnode = pnode.Nodes[pnode.Nodes.Count - 1];
                    }
                    else
                    {
                        TreeNode node = new TreeNode(headTexts[i]);
                        if (i == 0) tnc.Nodes.Add(node);
                        else pnode.Nodes.Add(node);
                        pnode = node;
                    }
                }
            }

            return tnc;
        }

        static private int getMaxLevel(TreeView treeView)
        {
            TreeNode treeNode = null;
            foreach (TreeNode node in treeView.Nodes)
            {
                TreeNode _node = getMaxLevelNode(node);
                if (treeNode == null)
                {
                    treeNode = _node;
                }
                else
                {
                    if (treeNode.Level < _node.Level)
                        treeNode = _node;
                }
            }
            return treeNode == null ? 0 : treeNode.Level;
        }

        /// <summary>
        /// 返回最深的节点（如果有多个，返回第一个）
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        static private TreeNode getMaxLevelNode(TreeNode node)
        {
            if (node == null)
                return null;
            if (node.Nodes.Count > 0)
            {
                bool hasChild = false;
                foreach (TreeNode _node in node.Nodes)
                {
                    if (_node.Nodes.Count > 0)
                    {
                        hasChild = true;
                        break;
                    }

                }
                if (hasChild)
                {
                    TreeNode treeNode = null;
                    foreach (TreeNode _node in node.Nodes)
                    {
                        TreeNode tempNode = getMaxLevelNode(_node);
                        if (treeNode == null)
                        {
                            treeNode = tempNode;
                        }
                        else
                        {
                            if (treeNode.Level < tempNode.Level)
                                treeNode = tempNode;
                        }
                    }
                    return treeNode;
                }
                else
                {
                    return node.Nodes[0];
                }
            }
            else
            {
                return node;
            }
        }

        /// <summary>
        /// 返回当前节点占用多少列
        /// </summary>
        /// <param name="node"></param>
        /// <returns>（因为包含本身，所以至少占用一列）</returns>
        static private int getNodeColCount(TreeNode node)
        {
            int colCount = getChildsCount(node, 0);
            return colCount == 0 ? 1 : colCount;
        }

        static private int getChildsCount(TreeNode node, int count)
        {
            if (node == null)
                return count;
            if (node.Nodes.Count < 1)
                return count;
            foreach (TreeNode _node in node.Nodes)
            {
                if (_node.Nodes.Count > 0)
                {
                    count = getChildsCount(_node, count);
                }
                else
                {
                    count++;
                }
            }
            return count;
        }

        static private int getChildsRowCount(TreeNode node, int maxLevel)
        {
            if (node == null || maxLevel < 1)
                return 1;
            return node.Nodes.Count > 0 ? 1 : maxLevel - node.Level;
        }

        /// <summary>
        /// 弹出对话框，选择文件保存路径
        /// </summary>
        /// <returns>返回目标文件的路径，如果用户取消选择则返回null</returns>
        static private String GetFileName()
        {
            SaveFileDialog dialog = new SaveFileDialog();
            dialog.DefaultExt = ".xls";
            dialog.AddExtension = true;
            dialog.CheckPathExists = true;
            dialog.Filter = "Excel电子表格 (*.xls)|*.xls";
            DialogResult dialogResult = dialog.ShowDialog();
            if (!dialogResult.Equals(DialogResult.OK))
                return null;
            return dialog.FileName;
        }
    }
}
